OpenEdge Data Management:<br />DataServer for Microsoft SQL Server Additional record locking details

Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Additional record locking details

In SQL Server, if transactions are scoped within other transactions, nested or embedded, all commit/rollback operations on the nested transactions are ignored by SQL server. Not until the transaction nesting levels are reduced to zero are all the nested levels actually committed or rolled back. This is a restriction of the data source with ramifications illustrated by the following code:
DO TRANSACTION: 
    RUN STORED-PROC SP. 
    CLOSE STORED-PROC SP. 
    UPDATE record 2. 
    ASSIGN fld2 = X. 
END. 
If the stored procedure SP has a commit or rollback statement within its TSQL, the commit or rollback isn’t actually executed until the END statement in the 4GL, when the transaction level returns to zero. If record 2 is also updated by SP, then record 2 will be locked out to the 4GL transaction that’s also trying to update it. SP will continue to hold record 2 and lock out the 4GL update even if SP explicitly commits the update inside the stored procedure. To avoid this problem, it is necessary to recode the example as follows:
DO TRANSACTION: 
    RUN STORED-PROC SP. 
    CLOSE STORED-PROC SP. 
END. 
DO TRANSACTION: 
    UPDATE Record 2. 
    ASSIGN fld2 = X. 
END. 